import influxdb
import pandas as pd
import dateutil.parser as dtparser
import datetime
import os
import numpy as np
import random
#client = influxdb.DataFrameClient(host='192.168.38.176', port='8086', username='root', password='root', database='')
#client = influxdb.DataFrameClient(host='192.168.38.2', port='8086', username='root', password='root', database='')
client = influxdb.DataFrameClient(host='192.168.58.71', port='8086', username='root', password='root', database='')
database='order'
#client.drop_database(database)
#client.create_database(database)
#client = influxdb.DataFrameClient(host='192.168.38.152', port='8086', username='root', password='root', database='')
measure='order'
date=20200225
b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)
e = dtparser.parse(str(date)) + datetime.timedelta(hours=24)
query=f""" select * from "{database}"."autogen"."{measure}" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} """
result=client.query(query)
if result!={}:
    data=pd.DataFrame(result[measure])
else:
    data=pd.DataFrame()
#print(data)
def getholdtime(time1,time2):
    hold=time2-time1
    h=hold.seconds
    if ((time2.hour>=5) & (time1.hour<5)):
        h=h-5400
    return h
    pass
data=data.sort_values(['code','ttime'])
#data=data[data['code']!='601012.SH']
#data=data[data['code']!='600585.SH']
dd=data[data['filledVol']<data['placeVol']]
partialratio=dd['filledVol'].sum()/dd['placeVol'].sum()
dd2=data[data['filledVol']==0]
untradecode=list(dd['code'].unique())
untrade=data[data['code'].isin(untradecode)]
pd.set_option('display.max_rows',1000)
#print(untrade)
print(data['filledVol'].sum())
transactionRate=dd.shape[0]/data.shape[0]
transactionRate2=dd2.shape[0]/data.shape[0]
print(f"partialtradeRatio:{transactionRate-transactionRate2}({partialratio}),untradeRatio:{transactionRate2},")
data=data[data['filledVol']>0]
print(data['filledVol'].sum())
codeList=list(data['code'].unique())
mytrade=[]
for code in codeList:
    data0=data[data['code']==code]
    position=0
    cash=0
    openDirection=0
    
    for idx, row in data0.iterrows():
        direction=row['direction']
        vol=row['filledVol']
        price=row['placePrice']
        price=row['lastFilledPrice']
        if (position==0):#开仓
            if (direction=='buy'):
                openDirection=1
                position=position+vol
                cash=cash-vol*price-vol*price*0.0001
                opentime=idx
            else:
                openDirection=-1
                position=position-vol
                cash=cash+vol*price-vol*price*0.0012
                opentime=idx
        elif (position>0):
            if (direction=='buy'):
                position=position+vol
                cash=cash-vol*price-vol*price*0.0001
            else:
                if position>vol:
                    position=position-vol
                    cash=cash+vol*price-vol*price*0.0012
                    pass
                elif position==vol:
                    position=position-vol
                    cash=cash+vol*price-vol*price*0.0012
                    closetime=idx
                    hold=getholdtime(opentime,closetime)
                    mytrade.append({'code':code,'profit':cash,'direction':1,'hold':hold})
                    cash=0
                    pass
                else:
                    cash=cash+price*position-price*position*0.0012
                    closetime=idx
                    hold=getholdtime(opentime,closetime)
                    mytrade.append({'code':code,'profit':cash,'direction':1,'hold':hold})
                    position=position-vol
                    cash=-price*position-price*(-position)*0.0012
                    opentime=idx
                    pass
                pass
            pass
        elif (position<0):
            if (direction=='sell'):
                position=position-vol
                cash=cash+vol*price-vol*price*0.0012
            else:
                if (-position>vol):
                    position=position+vol
                    cash=cash-vol*price-vol*price*0.0001
                    pass
                elif (-position==vol):
                    position=position+vol
                    cash=cash-vol*price-vol*price*0.0001
                    closetime=idx
                    hold=getholdtime(opentime,closetime)
                    mytrade.append({'code':code,'profit':cash,'direction':-1,'hold':hold})
                    cash=0
                    pass
                else:
                    cash=cash-vol*price-vol*price*0.0001
                    closetime=idx
                    hold=getholdtime(opentime,closetime)
                    mytrade.append({'code':code,'profit':cash,'direction':-1,'hold':hold})
                    position=position+vol
                    cash=-price*position-price*position*0.0001
                    opentime=idx
                    pass
                pass
            pass
        pass
    pass

mytrade=pd.DataFrame(mytrade)
#mytrade=mytrade[mytrade['profit']<-1000]

#print(mytrade)
print(mytrade['profit'].sum())
print(f"total number:{mytrade.shape[0]}")
print(f"long number:{mytrade[mytrade['direction']>0].shape[0]}")
print(f"short number:{mytrade[mytrade['direction']<0].shape[0]}")
print(f"win number:{mytrade[mytrade['profit']>0].shape[0]}")
print(f"short win number:{mytrade[(mytrade['direction']<0) & (mytrade['profit']>0)].shape[0]}")
print(f"long win number:{mytrade[(mytrade['direction']>0) & (mytrade['profit']>0)].shape[0]}")
print(f"short profit:{mytrade[(mytrade['direction']<0)]['profit'].sum()}")
print(f"long profit:{mytrade[(mytrade['direction']>0)]['profit'].sum()}")
print(f"short win profit:{mytrade[(mytrade['direction']<0) & (mytrade['profit']>0)]['profit'].sum()}")
print(f"long win profit:{mytrade[(mytrade['direction']>0) & (mytrade['profit']>0)]['profit'].sum()}")
print(f"hold mean:{mytrade['hold'].mean()}")


